import json
from datetime import datetime

import requests
from urllib.parse import urljoin

import pymysql

BASE_URL = 'http://cmp.msa.gov.cn/cbdj_mgt/radioquery/busiCountsQuery.action?radioApplyQO.all_flag=1&radioApplyQO.counts_org_code=140000&radioApplyQO.radio_type=&radioApplyQO.apply_date_start=2024-10-01&radioApplyQO.apply_date_end=2024-12-31&page=1&rows=10'
headers = {
    'Accept': 'application/json, text/javascript, */*; q=0.01',
    # Content-Type设置为application/json，接口才能返回json数据
    'Content-Type': 'application/json;charset=utf-8'
}
cookies = {
    # OAMAuthnCookie_cmp.msa.gov.cn:80 参数用于登录认证
    'OAMAuthnCookie_cmp.msa.gov.cn:80': 'sbtLIqfEj5ul8hat9llNBXqn3bm8Lpkqo%2BYBP6jAOXaG0KNn%2FqpGJlF8snjAG62%2Fgn7cEGHyVpgblg6GKLCs8vS9p8kuLsohp2XohK87lS68PD4FJn8ibh1yL94gRaa0J0Lkancpb8zmf2FML8knHpBVJoWQTeYStWrmkcJmafCZcg658fT0T5NQAj1wReZ8wAsvWbURfzzmYeqXX2N5e2xeJAk9BOibgr2zKFyGFpRnSyfaUUeGT%2BpIW17h6vnwmtqjP24czi1aAqiQlgSVo0Im0NGCn0aMB%2FgT5eRvxJdXvPjzR4N7O4m5g4j2QQ8aoy54oIHWYNAdpnK7M%2By%2FP0tOvqiOA%2BwpnPNpZMEshyPllX%2ByWjqCzRQ3bA%2FYI5FQSFB076LxfAv6ZJA3uIV4hcxfmIhm8IylzsupGeZ%2Fuk45KJMapXeCOlQjj9l6clMolDW9T8M2fLn9qsb57E%2FEnULU0A3k5bxXd8DiSnnh%2FyY%3D',

    # OAMAuthnHintCookie和JSESSIONID 参数用于声明返回接口数据，不配置的会一直返回HTML
    'OAMAuthnHintCookie': '1',
    'JSESSIONID': 'fVBa2yUobnjAog9N8ZKdOI7-N0juzSSJz3ROuhtO5_91QLk0x3GT!-1812561480'
}

session = requests.Session()
response_lo = session.post(BASE_URL, headers=headers, cookies=cookies)
print(response_lo.text)
strData = json.loads(response_lo.text)


if response_lo.status_code == 200:
    # 数据库连接配置
    db_config = {
        'host': '191.254.8.220',
        'user': 'root',
        'password': 'Szmsa@520',
        'db': 'spiders',
        'charset': 'utf8mb4',
        'cursorclass': pymysql.cursors.DictCursor
    }
    # 表名和字段名
    table_name = 'xt_ship_radio_station_credntial'
    # 连接数据库
    connection = pymysql.connect(**db_config)

    try:
        # 开始事务
        with connection.cursor() as cursor:
            # 循环插入每条记录
            for record in strData:
                # 获取需要插入的字段名称
                fields = ', '.join(record.keys())
                # 拼装需要插入字段对应的%号个数
                values = ", ".join(['%s'] * len(record))
                # print("!!!"+fields)
                # print("~~~"+values)
                # names = record['agent_name']
                # print(names)
                print(tuple(record.keys()))
                # 构建插入语句
                insert_stmt = (
                    "INSERT INTO `{table}` ({fields}) "
                    "VALUES ({values})"
                ).format(
                    table=table_name,
                    fields=fields,
                    values=values
                )
                # 执行sql语句插入数据，record.values()用于获取字段中的值
                cursor.execute(insert_stmt, tuple(record.values()))
                # print("%%%" + insert_stmt)
                # print(">>>" + str(record.values()))
        # 提交事务
        connection.commit()
    finally:
        # 关闭数据库连接
        connection.close()